package com.project.frba.bi;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Collection;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.project.frba.bi.descriptor.Descriptor;
import com.project.frba.bifront.client.model.Cube;
import com.project.frba.bifront.client.model.CubeDataRow;


public class XLSExporter {
	
	private Descriptor descriptor;
	
	public XLSExporter(Descriptor descriptor){
		this.descriptor = descriptor;
	}
	
	public void export(String filename, Cube cube) throws IOException{
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		createHeader(workbook, sheet, cube.getProperties());
		createBody(workbook, sheet, cube);
		
		//save
		FileOutputStream out = new FileOutputStream(filename);
		workbook.write(out);
		out.close();
	}
	
	private void createHeader(HSSFWorkbook workbook, HSSFSheet sheet, Collection<String> properties) {
		HSSFRow row = sheet.createRow(0);
		HSSFCell metricLabelCell = row.createCell(0);
		metricLabelCell.setCellValue(new HSSFRichTextString(descriptor.getLabel()));
		metricLabelCell.setCellStyle(getHeaderStyle(workbook));
		sheet.autoSizeColumn((short)0);
		int j = 1;
		for (String propertyName : properties) {
			sheet.autoSizeColumn((short)j);
			HSSFCell propertyLabelCell = row.createCell(j);
			propertyLabelCell.setCellValue(new HSSFRichTextString(descriptor.getPropertyByName(propertyName).getLabel()));
			propertyLabelCell.setCellStyle(getHeaderStyle(workbook));
			j++;
		}
		
	}
	
	private void createBody(HSSFWorkbook workbook, HSSFSheet sheet, Cube cube) {
		sheet.autoSizeColumn((short)1, true);
		sheet.autoSizeColumn((short)2, true);
		int i = 1;
		for (CubeDataRow dataRow : cube.getRows()) {
			HSSFRow row = sheet.createRow(i);
			HSSFCell metricCell = row.createCell(0);
			metricCell.setCellValue(new HSSFRichTextString(dataRow.getData()));
			metricCell.setCellStyle(getMetricStyle(workbook));
			int j = 1;
			for (String propertyName : cube.getProperties()) {
				HSSFCell propertyCell = row.createCell(j);
				propertyCell.setCellValue(new HSSFRichTextString(dataRow.getValue(propertyName)));
				propertyCell.setCellStyle(getPropertyStyle(workbook));
				j++;
			}
			i++;
		}
	}
	
	public HSSFCellStyle getHeaderStyle(HSSFWorkbook wb){
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFFont font = wb.createFont();
		font.setFontName("Arial");
		font.setFontHeightInPoints((short)12);
		font.setColor(HSSFColor.WHITE.index);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellStyle.setFont(font);
		return cellStyle;
	}

	public HSSFCellStyle getMetricStyle(HSSFWorkbook wb){
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		return cellStyle;
	}
	
	public HSSFCellStyle getPropertyStyle(HSSFWorkbook wb){
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont font = wb.createFont();
		font.setFontName("Arial");
		font.setFontHeightInPoints((short)10);
		cellStyle.setFont(font);
		return cellStyle;
	}
}
